﻿Public Class EntityTablas
    Shared ctx As New EntidadesTablas(My.Settings.CadenaAPPCATablas)

#Region "Inicio de sesión"
    Public Shared Function Login(ByVal user As String, ByVal pass As String) As Boolean
        Dim usu = (From users In ctx.USUARIOS
                   From per In users.PERFILES
                   Where users.USUARIO = user AndAlso users.CONTRASENA = pass AndAlso users.ESTADO = 1 AndAlso per.IDPERFIL = My.Settings.IDPerfil
                   Select users).ToList()

        If usu.Count = 1 Then
            For Each u In usu
                With SesionActiva
                    .IdUsuario = u.IDUSUARIO
                    .Nombre = String.Format("{0} {1}", u.NOMBRE, u.APELLIDOS)
                    .Usuario = u.USUARIO
                    .Sucursal = u.DETALLE_SUCURSAL_OFICINA.SUCURSALES.NOMBRE
                    .Oficina = u.DETALLE_SUCURSAL_OFICINA.OFICINAS.NOMBRE_OFICINA
                    .IdSucursalOficina = u.IDDETALLE_SUCURSAL_OFICINA
                End With
            Next
            Return True
        Else
            Return False
        End If
    End Function
#End Region

#Region "Gestiones por oficina"
    Public Shared Sub CargarOficinas(ByVal cbo As ComboBox)
        Dim ofi = (From o In ctx.OFICINAS.ToList()
                   Select o).ToList()

        cbo.DataSource = ofi
        cbo.DisplayMember = "NOMBRE_OFICINA"
        cbo.ValueMember = "IDOFICINA"
        cbo.SelectedValue = -1
    End Sub

    Public Shared Function AgregarGestion(ByVal ges As GESTIONES)
        Try
            ctx.GESTIONES.AddObject(ges)
            ctx.SaveChanges()
            Return ges.IDGESTION 'Después de SaveChanges(), EntityFramework carga el objeto 'ges' con los datos y así retornamos el ID recien agregado
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Shared Sub AgregarOFGE(ByVal ges As DETALLE_OFICINA_GESTIONES)
        Try
            ctx.DETALLE_OFICINA_GESTIONES.AddObject(ges)
            ctx.SaveChanges()
        Catch ex As UpdateException
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub CargarGestionesXOficinas(ByVal grid As DataGridView, ByVal ido As Integer)
        Dim ofi = (From o In ctx.DETALLE_OFICINA_GESTIONES
                   Where o.OFICINAS.IDOFICINA = ido
                   Select o.GESTIONES.IDGESTION, Código = o.GESTIONES.CODIGO, Gestión = o.GESTIONES.NOMBRE, Tiempo = o.GESTIONES.TIEMPO).ToList()

        grid.DataSource = ofi
        grid.Columns(0).Visible = False
    End Sub

    Public Shared Sub ActualizarGestion(ByVal idg As Integer, ByVal cod As String, ByVal nombre As String, ByVal tiempo As Integer)
        Dim gest As GESTIONES = (From g In ctx.GESTIONES.ToList() Where g.IDGESTION = idg).SingleOrDefault()
        Try
            With gest
                .CODIGO = cod
                .NOMBRE = nombre
                .TIEMPO = tiempo
            End With
            ctx.SaveChanges()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub EliminarGestion(ByVal idges As Integer)
        Dim ges = (From g In ctx.GESTIONES
                  Where g.IDGESTION = idges).SingleOrDefault
        Dim det = (From d In ctx.DETALLE_OFICINA_GESTIONES
                 Where d.IDGESTION = idges).SingleOrDefault
        Try
            ctx.DETALLE_OFICINA_GESTIONES.DeleteObject(det)
            ctx.GESTIONES.DeleteObject(ges)
            ctx.SaveChanges()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
#End Region

#Region "Gestionar requisitos"
    Public Shared Sub CargarRequisitos(ByVal grid As DataGridView, ByVal idg As Integer)
        Dim reqs = (From r In ctx.REQUISITOS
                   Where r.IDGESTION = idg
                   Select r.IDREQUISITO, Nombre = r.NOMBRE_REQUISITO, Opcional = If(r.OPCIONAL = 1, True, False)).ToList()

        grid.DataSource = reqs
        grid.Columns(0).Visible = False
    End Sub

    Public Shared Sub AgregarRequisito(ByVal req As REQUISITOS)
        Try
            ctx.REQUISITOS.AddObject(req)
            ctx.SaveChanges()
        Catch ex As UpdateException
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub ActualizarRequisito(ByVal idr As Integer, ByVal idg As Integer, ByVal nombre As String, ByVal opt As Integer)
        Dim requisito As REQUISITOS = (From r In ctx.REQUISITOS.ToList() Where r.IDREQUISITO = idr).SingleOrDefault()

        Try
            With requisito
                .IDGESTION = idg
                .NOMBRE_REQUISITO = nombre
                .OPCIONAL = opt
            End With
            ctx.SaveChanges()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub eliminarRequisito(ByVal idr As Integer)
        Dim re = (From r In ctx.REQUISITOS Where r.IDREQUISITO = idr).SingleOrDefault
        Try
            ctx.REQUISITOS.DeleteObject(re)
            ctx.SaveChanges()
            MsgBox("Requisito eliminado")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
#End Region

#Region "Saltos y procesos"
    Public Shared Sub CargarPuestos(ByVal combo As ComboBox, ByVal ido As Integer)
        Dim puestos = (From p In ctx.PUESTO Where p.IDOFICINA = ido Select p).ToList
        combo.DisplayMember = "NOMBRE_PUESTO"
        combo.ValueMember = "IDPUESTO"
        combo.DataSource = puestos
    End Sub

    Public Shared Sub CargarSaltos(ByVal grid As DataGridView, ByVal idg As Integer)
        Dim sal = (From s In ctx.SALTOS
                   Where s.IDGESTION = idg
                   Order By s.NUMERO_SALTO
                   Select s.IDSALTO, Numero = s.NUMERO_SALTO).ToList()

        grid.DataSource = sal
        grid.Columns(0).Visible = False

    End Sub

    Public Shared Sub CargarSaltosCbo(ByVal combo As ComboBox, ByVal idg As Integer)
        Dim sal = (From s In ctx.SALTOS
                   Where s.IDGESTION = idg
                   Order By s.NUMERO_SALTO
                   Select s.IDSALTO, s.NUMERO_SALTO).ToList()

        combo.DisplayMember = "NUMERO_SALTO"
        combo.ValueMember = "IDSALTO"
        combo.DataSource = sal
        combo.SelectedValue = -1
    End Sub

    Public Shared Sub AgregarSalto(ByVal salto As SALTOS)
        Dim paso As Integer = (From p In ctx.SALTOS.ToList()
                   Where p.NUMERO_SALTO = salto.NUMERO_SALTO AndAlso p.IDGESTION = salto.IDGESTION).Count()

        If paso < 1 Then
            Try
                ctx.SALTOS.AddObject(salto)
                ctx.SaveChanges()
            Catch ex As UpdateException
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Public Shared Function obtenerSalto(ByVal idsalto As Integer)
        Dim salto = (From s In ctx.SALTOS Where s.IDSALTO = idsalto Select s).SingleOrDefault
        Return salto
    End Function

    Public Shared Sub ActualizarSalto(ByVal ids As Integer, ByVal numero As Integer, ByVal puesto As Integer,
                                      ByVal ultimoSalto As Integer, ByVal minutos As Integer, ByVal decision As Integer,
                                      Optional ByVal saltoVer As Integer = -1,
                                      Optional ByVal saltoFalso As Integer = -1,
                                      Optional ByVal descripcion As String = "")
        Dim sal As SALTOS = (From s In ctx.SALTOS Where s.IDSALTO = ids).First
        Try
            With sal
                .NUMERO_SALTO = numero
                .IDPUESTO = puesto
                .ULTIMOSALTO = ultimoSalto
                .MINUTOS = minutos
                .DECISION = decision
                .IDSALTOV = saltoVer
                .IDSALTOF = saltoFalso
                .DESCRIPCION_DECISION = descripcion
            End With
            ctx.SaveChanges()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub EliminarSalto(ByVal idg As Integer)
        'Al eliminar el salto se eliminan los procesos en cascada definido en la base de datos
        Dim ultSalto = (From s In ctx.SALTOS.ToList Where s.IDGESTION = idg
                        Order By s.NUMERO_SALTO Descending
                        Select s).First()

        Dim salto = (From s In ctx.SALTOS.ToList Where s.IDSALTO = ultSalto.IDSALTO Select s).SingleOrDefault
        Try
            ctx.SALTOS.DeleteObject(salto)
            ctx.SaveChanges()
        Catch ex As UpdateException
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub AgregarProceso(ByVal proceso As PROCESOS)
        Try
            ctx.PROCESOS.AddObject(proceso)
            ctx.SaveChanges()
        Catch ex As UpdateException
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub CargarProcesos(ByVal grid As DataGridView, ByVal ids As Integer)
        Dim pro = (From p In ctx.PROCESOS
                   Where p.IDSALTO = ids
                   Order By p.NUMERO
                   Select p.IDPROCESO, p.NUMERO, p.DESCRIPCION).ToList()

        grid.DataSource = pro
        grid.Columns(0).Visible = False
    End Sub

    Public Shared Sub ActualizarProceso(ByVal idp As Integer, ByVal numero As Integer, ByVal Descripcion As String)
        Dim Proceso As PROCESOS = (From p In ctx.PROCESOS Where p.IDPROCESO = idp).First
        Try
            With Proceso
                .NUMERO = numero
                .DESCRIPCION = Descripcion
            End With
            ctx.SaveChanges()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub EliminarProceso(ByVal idsalto As Integer)
        Dim ultproceso = (From p In ctx.PROCESOS.ToList Where p.IDSALTO = idsalto
                        Order By p.NUMERO Descending
                        Select p).First()

        Dim pro = (From pr In ctx.PROCESOS.ToList Where pr.IDPROCESO = ultproceso.IDPROCESO Select pr).SingleOrDefault
        Try
            ctx.PROCESOS.DeleteObject(pro)
            ctx.SaveChanges()
        Catch ex As UpdateException
            MsgBox(ex.Message)
        End Try
    End Sub

    'Public Shared Sub MostrarRte(ByVal grid As DataGridView, ByVal lbl As Label, ByVal idg As Integer)
    Public Shared Function MostrarRte(ByVal grid As DevExpress.XtraGrid.GridControl, ByVal idg As Integer)
        Dim encabezado = (From e In ctx.GESTIONES
                         Where e.IDGESTION = idg
                         Select e.CODIGO, e.NOMBRE, e.TIEMPO).SingleOrDefault

        Dim Datos = (From d In ctx.PROCESOS
                     Where d.SALTOS.GESTIONES.IDGESTION = idg
                     Order By d.NUMERO
                     Select NSalto = d.SALTOS.NUMERO_SALTO, Minutos = d.SALTOS.MINUTOS,
                     Responsable = d.SALTOS.PUESTO.NOMBRE_PUESTO,
                     Numero = d.NUMERO, Descripcion = d.DESCRIPCION).ToList()

        'lbl.Text = String.Format("Codigo:{0}   Nombre:{1}   Tiempo:{2}", encabezado.CODIGO, encabezado.NOMBRE, encabezado.TIEMPO)

        grid.DataSource = Datos
        Return String.Format("{0} ({1}) - {2}", encabezado.NOMBRE, encabezado.CODIGO, MinutosAHoras(encabezado.TIEMPO))
    End Function

#End Region

End Class
